package Work;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Scanner;

import Work.Record;
import utils.Config;

/***** MySQL数据库连接类 *****/

public class Jdbc {

	Connection con;// 连接对象

	Statement state;// 执行对象

	public Jdbc() {

		// 注册数据库驱动
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		dataBase();
		init();// !!!

	}

	// 创建连接对象和执行对象
	private void dataBase() {
		// 获取连接对象
		try {
			this.con = DriverManager.getConnection(Config.jdbc_str, Config.Id, Config.Pwd);
			// 获取执行对象
			this.state = this.con.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 执行SQL语句(查询之外)
	public void sql(String str) {
		try {
			boolean execute = this.state.execute(str);
			if (execute) {
				System.out.println("执行失败");
			} else {
				System.out.println("执行成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 执行查询书籍SQL语句(使用集合返回)
	public ArrayList<Book> searchBook() {
		ArrayList<Book> array = new ArrayList<>();
		String str = "select * from book";
		try {
			// 获取结果集
			ResultSet resultSet = this.state.executeQuery(str);
			while (resultSet.next()) {
				Book book = new Book();
				// 补足八位，不够前导0
				book.setId(resultSet.getString("b_id"));
				book.setName(resultSet.getString("b_name"));
				book.setAuthor(resultSet.getString("author"));
				book.setPrice(Double.parseDouble(resultSet.getString("price")));
				book.setAmount(Integer.parseInt(resultSet.getString("amount")));

				array.add(book);
			}

			return array;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	// 执行查询书籍SQL语句(使用集合返回)
	public ArrayList<Book> searchR_Book() {
		ArrayList<Book> array = new ArrayList<>();
		String str = "select * from r_book";
		try {
			// 获取结果集
			ResultSet resultSet = this.state.executeQuery(str);
			while (resultSet.next()) {
				Book book = new Book();
				// 补足八位，不够前导0
				book.setId(resultSet.getString("b_id"));
				book.setName(resultSet.getString("b_name"));
				book.setAuthor(resultSet.getString("author"));
				book.setPrice(Double.parseDouble(resultSet.getString("price")));
				array.add(book);
			}
			return array;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 执行查询读者信息表SQL语句(使用集合返回)
	public ArrayList<Reader> searchReader() {
		ArrayList<Reader> array = new ArrayList<>();
		String str = "select * from reader";
		try {
			// 获取结果集
			ResultSet resultSet = this.state.executeQuery(str);
			while (resultSet.next()) {
				Reader reader = new Reader();
				// 补足八位，不够前导0
				reader.setID(resultSet.getString("r_id"));
				reader.setName(resultSet.getString("r_name"));
				reader.setNumber(resultSet.getString("r_number"));
				reader.setInstitute(resultSet.getString("institute"));
				reader.setAmount(resultSet.getString("r_amount"));
				array.add(reader);
			}

			return array;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 执行查询图书管理员信息表SQL语句(使用集合返回)
	public ArrayList<Librarian> searchLibrarian() {
		ArrayList<Librarian> array = new ArrayList<>();
		String str = "select * from librarian";
		try {
			// 获取结果集
			ResultSet resultSet = this.state.executeQuery(str);
			while (resultSet.next()) {
				Librarian librarian = new Librarian();
				// 补足八位，不够前导0
				librarian.setID(resultSet.getString("l_id"));
				librarian.setName(resultSet.getString("l_name"));
				librarian.setNumber(resultSet.getString("l_number"));

				array.add(librarian);
			}

			return array;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 执行查询图书管理员信息表SQL语句(使用集合返回)
	public ArrayList<Record> searchRecord() {
		ArrayList<Record> array = new ArrayList<>();
		String str = "select * from record";
		try {
			// 获取结果集
			ResultSet resultSet = this.state.executeQuery(str);
			while (resultSet.next()) {
				Record record = new Record();
				// 补足八位，不够前导0
				record.setB_ID(resultSet.getString("b_id"));
				record.setR_ID(resultSet.getString("r_id"));
				record.setBorrow_time(resultSet.getString("borrow_time"));
				record.setReturn_time(resultSet.getString("return_time"));
				record.setReborrow_time(resultSet.getString("reborrow_time"));
				record.setLib_confirm(resultSet.getString("lib_confirm"));

				array.add(record);
			}
			return array;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	// 查询记录信息
	public ArrayList<Message> searchMessage() {
		ArrayList<Message> array = new ArrayList<>();
		String str = "select * from message";
		try {
			// 获取结果集
			ResultSet resultSet = this.state.executeQuery(str);
			while (resultSet.next()) {
				Message message = new Message();
				// 补足八位，不够前导0
				message.setId(resultSet.getString("id"));
				message.setBook(resultSet.getString("book"));
				message.setType(resultSet.getString("type"));
				message.setTime(resultSet.getString("time"));
				message.setInfo(resultSet.getString("info"));

				array.add(message);
			}

			return array;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	// 执行查询书籍SQL语句(返回图书列表)
	public ArrayList<Book> findBook(int i) {
		ArrayList<Book> array = new ArrayList<>();
		int count = 0;// 计数变量
		Scanner sc = new Scanner(System.in);
		String str;
		try {
			if (i == 1) {// 选择1 按图书id查询
				System.out.println("您想要查询的书籍编号是:");
				str = sc.next();
				String sqlstr1 = "select * from book where b_id = '" + str + "\'";
				ResultSet resultSet1 = this.state.executeQuery(sqlstr1);
				while (resultSet1.next()) {
					Book book = new Book();
					book.setId(resultSet1.getString("b_id"));
					book.setName(resultSet1.getString("b_name"));
					book.setAuthor(resultSet1.getString("author"));
					book.setPrice(Double.parseDouble(resultSet1.getString("price")));
					book.setAmount(Integer.parseInt(resultSet1.getString("amount")));
					count++;
					array.add(book);
				}
			} else if (i == 2) {// 选择2 按书名查询
				System.out.println("您想要查询的书籍名称是:");
				str = sc.next();
				String sqlstr2 = "select * from book where b_name = '" + str + "\'";
				ResultSet resultSet2 = this.state.executeQuery(sqlstr2);
				while (resultSet2.next()) {
					Book book = new Book();
					book.setId(resultSet2.getString("b_id"));
					book.setName(resultSet2.getString("b_name"));
					book.setAuthor(resultSet2.getString("author"));
					book.setPrice(Double.parseDouble(resultSet2.getString("price")));
					book.setAmount(Integer.parseInt(resultSet2.getString("amount")));
					count++;
					array.add(book);
				}
			} else if (i == 3) {// 选择3 按作者查询
				System.out.println("您想要查询的书籍的作者姓名是:");
				str = sc.next();
				String sqlstr3 = "select * from book where author = '" + str + "\'";
				ResultSet resultSet3 = this.state.executeQuery(sqlstr3);
				while (resultSet3.next()) {
					Book book = new Book();
					book.setId(resultSet3.getString("b_id"));
					book.setName(resultSet3.getString("b_name"));
					book.setAuthor(resultSet3.getString("author"));
					book.setPrice(Double.parseDouble(resultSet3.getString("price")));
					book.setAmount(Integer.parseInt(resultSet3.getString("amount")));
					count++;
					array.add(book);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (count == 0)
			return null;
		// System.out.println("\n共查询到" + count + "条结果：");
		return array;
	}

	// 执行查询读者SQL语句(返回读者列表)
	public ArrayList<Reader> findReader(int i) {
		ArrayList<Reader> array = new ArrayList<>();
		int count = 0;// 计数变量
		Scanner sc = new Scanner(System.in);
		String str;
		try {
			if (i == 1) {// 选择1 按读者id查询
				System.out.println("您想要查询的读者编号是:");
				str = sc.next();
				String sqlstr1 = "select * from reader where r_id = '" + str + "\'";
				ResultSet resultSet1 = this.state.executeQuery(sqlstr1);
				while (resultSet1.next()) {
					Reader reader = new Reader();
					reader.setID(resultSet1.getString("r_id"));
					reader.setName(resultSet1.getString("r_name"));
					reader.setNumber(resultSet1.getString("r_number"));
					reader.setInstitute(resultSet1.getString("institute"));
					count++;
					array.add(reader);
				}
			} else if (i == 2) {// 选择2 按读者姓名查询
				System.out.println("您想要查询的读者姓名是:");
				str = sc.next();
				String sqlstr2 = "select * from reader where r_name = '" + str + "\'";
				ResultSet resultSet2 = this.state.executeQuery(sqlstr2);
				while (resultSet2.next()) {
					Reader reader = new Reader();
					reader.setID(resultSet2.getString("r_id"));
					reader.setName(resultSet2.getString("r_name"));
					reader.setNumber(resultSet2.getString("r_number"));
					reader.setInstitute(resultSet2.getString("institute"));
					count++;
					array.add(reader);
				}
			} else if (i == 3) {// 选择3 按读者电话查询
				System.out.println("您想要查询的读者的电话是:");
				str = sc.next();
				String sqlstr3 = "select * from reader where r_number = '" + str + "\'";
				ResultSet resultSet3 = this.state.executeQuery(sqlstr3);
				while (resultSet3.next()) {
					Reader reader = new Reader();
					reader.setID(resultSet3.getString("r_id"));
					reader.setName(resultSet3.getString("r_name"));
					reader.setNumber(resultSet3.getString("r_number"));
					reader.setInstitute(resultSet3.getString("institute"));
					count++;
					array.add(reader);
				}
			} else if (i == 4) {// 选择4 按读者电话查询
				System.out.println("您想要查询的读者的学院是:");
				str = sc.next();
				String sqlstr4 = "select * from reader where institute = '" + str + "\'";
				ResultSet resultSet4 = this.state.executeQuery(sqlstr4);
				while (resultSet4.next()) {
					Reader reader = new Reader();
					reader.setID(resultSet4.getString("r_id"));
					reader.setName(resultSet4.getString("r_name"));
					reader.setNumber(resultSet4.getString("r_number"));
					reader.setInstitute(resultSet4.getString("institute"));
					count++;
					array.add(reader);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (count == 0)
			return null;
		System.out.println("\n共查询到" + count + "条结果：");
		return array;
	}

	// 执行查询书籍SQL语句(返回管理员列表)
	public ArrayList<Librarian> findLibrarian(int i) {
		ArrayList<Librarian> array = new ArrayList<>();
		int count = 0;// 计数变量
		Scanner sc = new Scanner(System.in);
		String str;
		try {
			if (i == 1) {// 选择1 按读者id查询
				System.out.println("您想要查询的管理员编号是:");
				str = sc.next();
				String sqlstr1 = "select * from librarian where l_id = '" + str + "\'";
				ResultSet resultSet1 = this.state.executeQuery(sqlstr1);
				while (resultSet1.next()) {
					Librarian librarian = new Librarian();
					librarian.setID(resultSet1.getString("l_id"));
					librarian.setName(resultSet1.getString("l_name"));
					librarian.setNumber(resultSet1.getString("l_number"));
					count++;
					array.add(librarian);
				}
			} else if (i == 2) {// 选择2 按读者姓名查询
				System.out.println("您想要查询的管理员姓名是:");
				str = sc.next();
				String sqlstr2 = "select * from librarian where l_name = '" + str + "\'";
				ResultSet resultSet2 = this.state.executeQuery(sqlstr2);
				while (resultSet2.next()) {
					Librarian librarian = new Librarian();
					librarian.setID(resultSet2.getString("l_id"));
					librarian.setName(resultSet2.getString("l_name"));
					librarian.setNumber(resultSet2.getString("l_number"));
					count++;
					array.add(librarian);
				}
			} else if (i == 3) {// 选择3 按读者电话查询
				System.out.println("您想要查询的管理员电话的电话是:");
				str = sc.next();
				String sqlstr3 = "select * from reader where l_number = '" + str + "\'";
				ResultSet resultSet3 = this.state.executeQuery(sqlstr3);
				while (resultSet3.next()) {
					Librarian librarian = new Librarian();
					librarian.setID(resultSet3.getString("l_id"));
					librarian.setName(resultSet3.getString("l_name"));
					librarian.setNumber(resultSet3.getString("l_number"));
					count++;
					array.add(librarian);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (count == 0)
			return null;
		System.out.println("\n共查询到" + count + "条结果：");
		return array;
	}

	// ui中的书籍查询
	public ArrayList<Book> uifindBook(Jdbc db, int i, String s) {
		ArrayList<Book> array = new ArrayList<>();
		PrintList printlist = new PrintList();
		int count = 0;// 计数变量
		try {
			if (i == 1) {// 选择1 按图书id查询
				String sqlstr1 = "select * from book where b_id = '" + s + "\'";
				ResultSet resultSet1 = this.state.executeQuery(sqlstr1);
				Book book = new Book();
				while (resultSet1.next()) {
					// Book book = new Book();
					book.setId(resultSet1.getString("b_id"));
					book.setName(resultSet1.getString("b_name"));
					book.setAuthor(resultSet1.getString("author"));
					book.setPrice(Double.parseDouble(resultSet1.getString("price")));
					book.setAmount(Integer.parseInt(resultSet1.getString("amount")));
					count++;
					array.add(book);
				}
				printlist.printbookList(array);
				System.out.println(s + "和" + i);
			} else if (i == 2) {// 选择2 按书名查询
				String sqlstr2 = "select * from book where b_name like '" + s + "\'";
				ResultSet resultSet2 = this.state.executeQuery(sqlstr2);
				while (resultSet2.next()) {
					Book book = new Book();
					book.setId(resultSet2.getString("b_id"));
					book.setName(resultSet2.getString("b_name"));
					book.setAuthor(resultSet2.getString("author"));
					book.setPrice(Double.parseDouble(resultSet2.getString("price")));
					book.setAmount(Integer.parseInt(resultSet2.getString("amount")));
					count++;
					array.add(book);
				}
			} else if (i == 3) {// 选择3 按作者查询
				String sqlstr3 = "select * from book where author = '" + s + "\'";
				ResultSet resultSet3 = this.state.executeQuery(sqlstr3);
				while (resultSet3.next()) {
					Book book = new Book();
					book.setId(resultSet3.getString("b_id"));
					book.setName(resultSet3.getString("b_name"));
					book.setAuthor(resultSet3.getString("author"));
					book.setPrice(Double.parseDouble(resultSet3.getString("price")));
					book.setAmount(Integer.parseInt(resultSet3.getString("amount")));
					count++;
					array.add(book);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (count == 0)
			return null;

		return array;
	}

	// ui中的查询
	// 执行查询读者SQL语句(返回读者列表)
	public ArrayList<Reader> uifindReader(Jdbc db, int i, String s) {
		ArrayList<Reader> array = new ArrayList<>();
		int count = 0;// 计数变量
		try {
			if (i == 1) {// 选择1 按读者id查询
				String sqlstr1 = "select * from reader where r_id = '" + s + "\'";
				ResultSet resultSet1 = this.state.executeQuery(sqlstr1);
				while (resultSet1.next()) {
					Reader reader = new Reader();
					reader.setID(resultSet1.getString("r_id"));
					reader.setName(resultSet1.getString("r_name"));
					reader.setNumber(resultSet1.getString("r_number"));
					reader.setInstitute(resultSet1.getString("institute"));
					reader.setAmount(resultSet1.getString("r_amount"));
					count++;
					array.add(reader);
				}
			} else if (i == 2) {// 选择2 按读者姓名查询
				String sqlstr2 = "select * from reader where r_name = '" + s + "\'";
				ResultSet resultSet2 = this.state.executeQuery(sqlstr2);
				while (resultSet2.next()) {
					Reader reader = new Reader();
					reader.setID(resultSet2.getString("r_id"));
					reader.setName(resultSet2.getString("r_name"));
					reader.setNumber(resultSet2.getString("r_number"));
					reader.setInstitute(resultSet2.getString("institute"));
					reader.setAmount(resultSet2.getString("r_amount"));
					count++;
					array.add(reader);
				}
			} else if (i == 3) {// 选择3 按读者电话查询
				String sqlstr3 = "select * from reader where r_number = '" + s + "\'";
				ResultSet resultSet3 = this.state.executeQuery(sqlstr3);
				while (resultSet3.next()) {
					Reader reader = new Reader();
					reader.setID(resultSet3.getString("r_id"));
					reader.setName(resultSet3.getString("r_name"));
					reader.setNumber(resultSet3.getString("r_number"));
					reader.setInstitute(resultSet3.getString("institute"));
					reader.setAmount(resultSet3.getString("r_amount"));
					count++;
					array.add(reader);
				}
			} else if (i == 4) {// 选择4 按读者学院查询
				String sqlstr4 = "select * from reader where institute = '" + s + "\'";
				ResultSet resultSet4 = this.state.executeQuery(sqlstr4);
				while (resultSet4.next()) {
					Reader reader = new Reader();
					reader.setID(resultSet4.getString("r_id"));
					reader.setName(resultSet4.getString("r_name"));
					reader.setNumber(resultSet4.getString("r_number"));
					reader.setInstitute(resultSet4.getString("institute"));
					reader.setAmount(resultSet4.getString("r_amount"));
					count++;
					array.add(reader);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (count == 0)
			return null;
		System.out.println("\n共查询到" + count + "条结果：");
		return array;
	}

//ui中的
	// 执行查询书籍SQL语句(返回管理员列表)
	public ArrayList<Librarian> uifindLibrarian(Jdbc db, int i, String s) {
		ArrayList<Librarian> array = new ArrayList<>();
		int count = 0;// 计数变量
		try {
			if (i == 1) {// 选择1 按读者id查询
				String sqlstr1 = "select * from librarian where l_id = '" + s + "\'";
				ResultSet resultSet1 = this.state.executeQuery(sqlstr1);
				while (resultSet1.next()) {
					Librarian librarian = new Librarian();
					librarian.setID(resultSet1.getString("l_id"));
					librarian.setName(resultSet1.getString("l_name"));
					librarian.setNumber(resultSet1.getString("l_number"));
					count++;
					array.add(librarian);
				}
			} else if (i == 2) {// 选择2 按读者姓名查询
				String sqlstr2 = "select * from librarian where l_name = '" + s + "\'";
				ResultSet resultSet2 = this.state.executeQuery(sqlstr2);
				while (resultSet2.next()) {
					Librarian librarian = new Librarian();
					librarian.setID(resultSet2.getString("l_id"));
					librarian.setName(resultSet2.getString("l_name"));
					librarian.setNumber(resultSet2.getString("l_number"));
					count++;
					array.add(librarian);
				}
			} else if (i == 3) {// 选择3 按读者电话查询
				String sqlstr3 = "select * from reader where l_number = '" + s + "\'";
				ResultSet resultSet3 = this.state.executeQuery(sqlstr3);
				while (resultSet3.next()) {
					Librarian librarian = new Librarian();
					librarian.setID(resultSet3.getString("l_id"));
					librarian.setName(resultSet3.getString("l_name"));
					librarian.setNumber(resultSet3.getString("l_number"));
					count++;
					array.add(librarian);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (count == 0)
			return null;
		System.out.println("\n共查询到" + count + "条结果：");
		return array;
	}

	public ArrayList<Record> uifindRecord(Jdbc db, int i, String s) {
		ArrayList<Record> array = new ArrayList<>();
		int count = 0;// 计数变量
		try {
			if (i == 1) {// 选择1 按书籍id查询
				String sqlstr1 = "select * from record where b_id = '" + s + "\'";
				ResultSet resultSet = this.state.executeQuery(sqlstr1);
				while (resultSet.next()) {
					Record record = new Record();
					record.setB_ID(resultSet.getString("b_id"));
					record.setR_ID(resultSet.getString("r_id"));
					record.setBorrow_time(resultSet.getString("borrow_time"));
					record.setReturn_time(resultSet.getString("return_time"));
					record.setReborrow_time(resultSet.getString("reborrow_time"));
					record.setLib_confirm(resultSet.getString("lib_confirm"));
					count++;
					array.add(record);
				}
			} else if (i == 2) {// 选择1 按读者id查询
				String sqlstr1 = "select * from record where r_id = '" + s + "\'";
				ResultSet resultSet = this.state.executeQuery(sqlstr1);
				while (resultSet.next()) {
					Record record = new Record();
					record.setB_ID(resultSet.getString("b_id"));
					record.setR_ID(resultSet.getString("r_id"));
					record.setBorrow_time(resultSet.getString("borrow_time"));
					record.setReturn_time(resultSet.getString("return_time"));
					record.setReborrow_time(resultSet.getString("reborrow_time"));
					record.setLib_confirm(resultSet.getString("lib_confirm"));
					count++;
					array.add(record);
				}
			} else if (i == 3) {// 选择1 按读者id查询
				String sqlstr1 = "select * from record where lib_confirm = '" + s + "\'";
				ResultSet resultSet = this.state.executeQuery(sqlstr1);
				while (resultSet.next()) {
					Record record = new Record();
					record.setB_ID(resultSet.getString("b_id"));
					record.setR_ID(resultSet.getString("r_id"));
					record.setBorrow_time(resultSet.getString("borrow_time"));
					record.setReturn_time(resultSet.getString("return_time"));
					record.setReborrow_time(resultSet.getString("reborrow_time"));
					record.setLib_confirm(resultSet.getString("lib_confirm"));
					count++;
					array.add(record);
				}
			} else if (i == 4) {// 选择2 按读者姓名查询
				String sqlstr2 = "select * from record where borrow_time like '%" + s + "%'";
				System.out.println("1");
				ResultSet resultSet = this.state.executeQuery(sqlstr2);
				while (resultSet.next()) {
					Record record = new Record();
					record.setB_ID(resultSet.getString("b_id"));
					record.setR_ID(resultSet.getString("r_id"));
					record.setBorrow_time(resultSet.getString("borrow_time"));
					record.setReturn_time(resultSet.getString("return_time"));
					record.setReborrow_time(resultSet.getString("reborrow_time"));
					record.setLib_confirm(resultSet.getString("lib_confirm"));
					count++;
					array.add(record);
				}
			} else if (i == 5) {// 选择3 按读者电话查询
				String sqlstr3 = "select * from record where return_time like '%" + s + "%'";
				ResultSet resultSet = this.state.executeQuery(sqlstr3);
				while (resultSet.next()) {
					Record record = new Record();
					record.setB_ID(resultSet.getString("b_id"));
					record.setR_ID(resultSet.getString("r_id"));
					record.setBorrow_time(resultSet.getString("borrow_time"));
					record.setReturn_time(resultSet.getString("return_time"));
					record.setReborrow_time(resultSet.getString("reborrow_time"));
					record.setLib_confirm(resultSet.getString("lib_confirm"));
					count++;
					array.add(record);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (count == 0)
			return null;
		return array;
	}

	public String uifindLogin1(Jdbc db, String s1, String s2) {
	    Login Login = new Login();
	    try {
	        String sqlstr3 = "SELECT password, authority FROM login WHERE username = '" + s1 + "' AND authority = '" + s2 + "'";
	        ResultSet resultSet = this.state.executeQuery(sqlstr3);
	        if (resultSet.next()) {
	            Login.setNumber(resultSet.getString("password"));
	        } else {
	            return "error";
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    String pw = Login.getNumber();
	    return pw;
	}


	// UI书籍增加
	public void uiinsertbook(Jdbc db, String s1, String s2, String s3, String s4) {
		db.sql("insert into book (b_name, author, price, amount) " + "value ('" + s1 + "', '" + s2 + "', '" + s3
				+ "', '" + s4 + "')");
		return;

	}

	// UI读者增加
	public void uiinsertreader(Jdbc db, String s1, String s2, String s3, String s4) {
		db.sql("insert into reader (r_name, r_number, institute, r_amount) " + "value ('" + s1 + "', '" + s2 + "', '"
				+ s3 + "', '" + s4 + "')");
		return;

	}

	// UI管理员增加
	public void uiinsertlibrarian(Jdbc db, String s1, String s2) {
		db.sql("insert into librarian (l_name, l_number) " + "value ('" + s1 + "', '" + s2 + "')");
		return;
	}

	// UI用户登录信息增加
	public void uiinsertlogin(Jdbc db, String s1, String s2, String s3) {
		db.sql("insert into login (username, password, authority) " + "value ('" + s1 + "', '" + s2 + "', '" + s3
				+ "')");
		return;
	}

	// ui书籍修改
	public void uiupdatebook(Jdbc db, String s1, String s2, int i) {
		if (i == 1) {
			db.sql("update book set b_name = '" + s2 + "' where b_id = '" + Integer.parseInt(s1) + "'");
		} else if (i == 2) {
			db.sql("update book set author = '" + s2 + "' where b_id = '" + Integer.parseInt(s1) + "'");
		} else if (i == 3) {
			db.sql("update book set price = '" + s2 + "' where b_id = '" + Integer.parseInt(s1) + "'");
		} else if (i == 4) {
			db.sql("update book set amount = '" + s2 + "' where b_id = '" + Integer.parseInt(s1) + "'");
		}
	}

	// ui读者修改
	public void uiupdatereader(Jdbc db, String s1, String s2, int i) {
		if (i == 1) {
			db.sql("update reader set r_name = '" + s2 + "' where r_id = '" + Integer.parseInt(s1) + "'");
		} else if (i == 2) {
			db.sql("update reader set r_number = '" + s2 + "' where r_id = '" + Integer.parseInt(s1) + "'");
		} else if (i == 3) {
			db.sql("update reader set institute = '" + s2 + "' where r_id = '" + Integer.parseInt(s1) + "'");
		} else if (i == 4) {
			db.sql("update reader set r_amount = '" + s2 + "' where r_id = '" + Integer.parseInt(s1) + "'");
		}
	}

	// ui管理员修改
	public void uiupdatelibrarian(Jdbc db, String s1, String s2, int i) {
		if (i == 1) {
			db.sql("update librarian set l_name = '" + s2 + "' where l_id = '" + Integer.parseInt(s1) + "'");
		} else if (i == 2) {
			db.sql("update librarian set l_number = '" + s2 + "' where l_id = '" + Integer.parseInt(s1) + "'");
		}
	}

	// ui书籍删除
	public void uideletebook(Jdbc db, String s, int i) {
		if (i == 1) {
			db.sql("delete from book where b_id = '" + Integer.parseInt(s) + "'");
		} else if (i == 2) {
			db.sql("delete from book where b_name = '" + s + "'");
		} else if (i == 3) {
			db.sql("delete from book where author = '" + s + "'");
		}
	}

	// ui读者删除
	public void uideletereader(Jdbc db, String s, int i) {
		if (i == 1) {
			db.sql("delete from reader where r_id = '" + Integer.parseInt(s) + "'");
		} else if (i == 2) {
			db.sql("delete from reader where r_name = '" + s + "'");
		} else if (i == 3) {
			db.sql("delete from reader where r_number = '" + s + "'");
		} else if (i == 4) {
			db.sql("delete from reader where institute = '" + s + "'");
		}
	}

	// ui管理员删除
	public void uideletelibrarian(Jdbc db, String s, int i) {
		if (i == 1) {
			db.sql("delete from librarian where l_id = '" + Integer.parseInt(s) + "'");
		} else if (i == 2) {
			db.sql("delete from librarian where l_name = '" + s + "'");
		} else if (i == 3) {
			db.sql("delete from librarian where l_number = '" + s + "'");
		}
	}

	// ui借阅系统
	public void uiborrowsystem(Jdbc db, String s1, String s2, String s3, int i) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String now = df.format(System.currentTimeMillis());
		if (i == 1) {
			db.sql("insert into record (b_id, r_id, borrow_time) value (" + s1 + ", " + s2 + ", '" + now + "')");
		} else if (i == 2) {
			db.sql("update record set return_time = '" + now + "' where b_id = " + s1 + " and r_id =" + s2);
			db.sql("update record set lib_confirm = " + s3 + " where b_id = " + s1 + " and r_id =" + s2);
		} else if (i == 3) {
			db.sql("update record set reborrow_time = '" + now + "' where b_id = " + s1 + " and r_id =" + s2);
		}
	}

	// 初始化数据库
	public void init() {
		// 新创建的数据库要开启使用
		String[] str = { "use library",
				"create table book (b_id INT AUTO_INCREMENT, PRIMARY KEY (b_id)"
						+ ", b_name varchar(30), author varchar(20), price double, amount int)",
				"insert into book (b_name , author, price, amount) value ('西游记', '吴承恩', 88.88, 2)",
				"insert into book (b_name , author, price, amount) value ('红楼梦', '曹雪芹', 99.99, 3)",
				"insert into book (b_name , author, price, amount) value ('三国演义', '罗贯中', 77.77, 1)",
				"insert into book (b_name , author, price, amount) value ('水浒传', '施耐庵', 66.66, 4)",
				"create table message (id INT AUTO_INCREMENT, PRIMARY KEY (id)"
						+ ", book varchar(30), type varchar(30), time varchar(30), info varchar(255))",
				"insert into message (book, type, time, info) value ('null', '创建表格 book', '"
						+ Calendar.getInstance().getTime() + "', 'ok')",
				"insert into message (book, type, time, info) value ('null', '导入demo数据', '"
						+ Calendar.getInstance().getTime() + "', 'ok')",
				"insert into message (book, type, time, info) value ('null', '创建表格 message', '"
						+ Calendar.getInstance().getTime() + "', 'ok')",
				"insert into message (book, type, time, info) value ('null', '导入初始化信息', '"
						+ Calendar.getInstance().getTime() + "', 'ok')" };

		// 验证语句（判断数据库中是否有表）
		try {
			ResultSet count = state.executeQuery(
					" SELECT COUNT(*) AS cou FROM information_schema.`TABLES` WHERE table_schema = 'library' AND (table_name = 'book' OR table_name = 'message') ");
			if (count.next()) {
				// 数据库与表格完整
				if (Integer.parseInt(count.getString("cou")) == 2) {
					System.out.println("初始化完成");
				}
				// 数据库存在表格不存在
				else if (Integer.parseInt(count.getString("cou")) == 0) {
					// 执行初始化语句
					for (String s : str) {
						try {
							this.state.execute(s);
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					System.out.println("初始化完成");
				} else {// 数据库存在但是表格数量不对，直接删除数据库重新初始化
					state.execute("DROP DATABASE library");
					init();
					System.out.println("初始化完成");
				}
			} else {// 数据库不存在则创建数据库，并且初始化
				state.execute("create  database library");
				// 新创建的数据库要开启使用
				state.execute("use library");
				init();
				System.out.println("初始化完成");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 存储操作记录
	public void record(String type, String book, String info) {
		try {
			state.execute("insert into message (book, type, time, info) " + "value ( '" + book + "', '" + type + "', '"
					+ Calendar.getInstance().getTime() + "', '" + info + "')");
			System.out.println("记录完成");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void close() {
		try {
			this.state.close();
			this.con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	// 出现异常情况，数据库和表格数量没变但是出现其他不可逆现象，使用这方法重置数据库
	public void restart() {
		try {
			// 删除数据库
			state.execute("DROP DATABASE library");
			System.out.println("删除表");
			// 创建数据库
			state.execute("create  database  library");
			System.out.println("创建表");
			init();
			System.out.println("重置完成");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
